﻿begin tran
set tran isolation level repeatable read
update DocGia set ho=N'Phạm' where ma_docgia=1
waitfor delay '0:0:5' --wait for 5ms
update Muon set ngayGio_muon='2000-07-07' where isbn=1 and ma_cuonsach=3
commit tran













-------------------------------
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- gan nhan RETRY
BEGIN TRANSACTION
BEGIN TRY

	set tran isolation level repeatable read
	update DocGia set ho=N'Phạm' where ma_docgia=1
	waitfor delay '0:0:5' --cho 5 ms
	update Muon set ngayGio_muon='2000-07-07' where isbn=1 and ma_cuonsach=3

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT 'Rollback Transaction'
	ROLLBACK TRANSACTION
	DECLARE @DoRetry bit; -- quyet dinh co lam lai transaction hay khong ?
	DECLARE @ErrorMessage varchar(500)
	SET @doRetry = 0;
	SET @ErrorMessage = ERROR_MESSAGE()
	IF ERROR_NUMBER() = 1205 -- Neu loi xay ra la deadlock
	BEGIN
		SET @doRetry = 1; -- Set @doRetry =1 cho Deadlock
	END
	IF @DoRetry = 1
	BEGIN
		SET @RetryCounter = @RetryCounter + 1 -- tang Retry Counter len 1
		IF (@RetryCounter > 3) -- kiem tra neu deadlock xay ra 3 lan thi khong lam nua
		BEGIN
			RAISERROR(@ErrorMessage, 18, 1) -- xuat thong bao loi
				
		END
		ELSE
		BEGIN
			WAITFOR DELAY '00:00:5' -- cho 5 ms
			GOTO RETRY	-- thuc hien lai trans
		END
	END
	ELSE
	BEGIN
		RAISERROR(@ErrorMessage, 18, 1)
	END
END CATCH